#!perl  -I../scripts/lib


use DBI;
use strict;

use Log;
use Connect;
use Run;


sub cmdline_error
	{
	my ($message) = @_;
	warn "$message\n" if (defined $message);
	print "Error command\n";
	exit;
	}

my ($db,$user,$pw,$file)=@ARGV;
unless (($db) and ($user) and ($pw) and ($file))
{
cmdline_error ("usage:/data/tools/bin/perl seed.pl <database> <user> <pw> <seed file location>");
}


my @report = ();
my @format_error = ();
my @error = ();
#my $file = "/home/cc_simgen/a.txt";
my $self = {};
my @allkey = qw(K1 K2 K3 K4 K5 K6 K7 K8 K9 K10 K11 K12 K13 K14 K15 K16 K17 K18 K19 K20);
my @dat = qw(DATREC DATINT DATEXP);
open(SEED,"$file") || die ("cannot open $file\n");
my @seed_a = <SEED>;
close (SEED);
my $dbh = Connect::db_con("$db","$user","$pw");
my $field_all = "IMEI,CODOPL,PRODUIT,DATREC,DATINT,DATEXP,K1,K2,K3,K4,K5,K6,K7,K8,K9,K10,K11,K12,K13,K14,K15,K16,K17,K18,K19,K20";

foreach my $seed (@seed_a)
	{
	chomp $seed;

	my @seed_nq = split(/;/,$seed);
	$seed =~ s/;/\";\"/g;
	$seed = "\"$seed\"";
	my @seed = split(/;/,$seed);
	$seed[1]=~ s/\"//g;
	if ($seed[1] == "")
		{
		$seed[1]=0;
		}
        my $str = join (',',@seed);

	my $seed_i = @seed;
	if ($seed_i != 26)
	{
	push @format_error,"the number of keys is not right:$str";
	next;	
	}
	($self->{'IMEI'},$self->{'OCDOPL'},$self->{'PRCDUIT'},$self->{'DATREC'},$self->{'DATINT'},$self->{'DATEXP'},$self->{'K1'}{'KEY'},$self->{'K2'}{'KEY'},$self->{'K3'}{'KEY'},$self->{'K4'}{'KEY'},$self->{'K5'}{'KEY'},$self->{'K6'}{'KEY'},$self->{'K7'}{'KEY'},$self->{'K8'}{'KEY'},$self->{'K9'}{'KEY'},$self->{'K10'}{'KEY'},$self->{'K11'}{'KEY'},$self->{'K12'}{'KEY'},$self->{'K13'}{'KEY'},$self->{'K14'}{'KEY'},$self->{'K15'}{'KEY'},$self->{'K16'}{'KEY'},$self->{'K17'}{'KEY'},$self->{'K18'}{'KEY'},$self->{'K19'}{'KEY'},$self->{'K20'}{'KEY'})=@seed_nq;

	my $flag = "";
	if (( $self->{'IMEI'}=~ /\d\d\d\d\d\d\d\d\d\d\d\d\d\d\d/))
	        {
		}
	else
		{
	        push @format_error,"the format of imei is not right:$str";
		next;
        	}

	foreach $_ (@dat)
                {
                if (($self->{$_} =~ /\d\d\d\d-\d\d-\d\d\s\d\d:\d\d:\d\d/) or ($self->{$_} == ""))
                	{
                	}
                else
                	{
			$flag = "1";
			last;
			}
                }
	foreach $_ (@allkey)
		{
		if ($self->{$_}{'KEY'} =~ /\D/)
			{
			if ($self->{$_}{'KEY'} ne "")
			{
                        $flag = "1";
                        last;
			}
                        }
		}

	if ($flag eq "1")
	{
        push @format_error,"the format of imei is not right:$str";
        next;
	}

	#check whether IMEI exists
	my $sql_stmnt = "SELECT IMEI FROM seed WHERE IMEI=$self->{'IMEI'}";
	my ($sth,$result) = Run::ex($sql_stmnt,$dbh);
	my @imei= $sth->fetchrow_array;
	Run::finish($sth);
	#if imei not exist
	if (! @imei)   
		{
		#if all keys are null,then report and not insert
		if (($self->{'K1'}{'KEY'} eq "") and ($self->{'K2'}{'KEY'} eq "") and ($self->{'K3'}{'KEY'} eq "") and ($self->{'K4'}{'KEY'} eq "") and ($self->{'K5'}{'KEY'} eq "") and ($self->{'K6'}{'KEY'} eq "") and ($self->{'K7'}{'KEY'} eq "") and ($self->{'K8'}{'KEY'} eq "") and ($self->{'K9'}{'KEY'} eq "") and ($self->{'K10'}{'KEY'} eq "") and ($self->{'K11'}{'KEY'} eq "") and ($self->{'K12'}{'KEY'} eq "") and ($self->{'K13'}{'KEY'} eq "") and ($self->{'K14'}{'KEY'} eq "") and ($self->{'K15'}{'KEY'} eq "") and ($self->{'K16'}{'KEY'} eq "") and ($self->{'K17'}{'KEY'} eq "") and ($self->{'K18'}{'KEY'} eq "") and ($self->{'K19'}{'KEY'} eq "") and ($self->{'K20'}{'KEY'} eq "")) 
			{
			push @report, "imei cannot insert,all keys are null:$str";
			}
		#if any key is 0,then report and not insert
		elsif (($self->{'K1'}{'KEY'} eq "0") or ($self->{'K2'}{'KEY'} eq "0") or ($self->{'K3'}{'KEY'} eq "0") or ($self->{'K4'}{'KEY'} eq "0") or ($self->{'K5'}{'KEY'} eq "0") or ($self->{'K6'}{'KEY'} eq "0") or ($self->{'K7'}{'KEY'} eq "0") or ($self->{'K8'}{'KEY'} eq "0") or ($self->{'K9'}{'KEY'} eq "0") or ($self->{'K10'}{'KEY'} eq "0") or ($self->{'K11'}{'KEY'} eq "0") or ($self->{'K12'}{'KEY'} eq "0") or ($self->{'K13'}{'KEY'} eq "0") or ($self->{'K14'}{'KEY'} eq "0") or ($self->{'K15'}{'KEY'} eq "0") or ($self->{'K16'}{'KEY'} eq "0") or ($self->{'K17'}{'KEY'} eq "0") or ($self->{'K18'}{'KEY'} eq "0") or ($self->{'K19'}{'KEY'} eq "0") or ($self->{'K20'}{'KEY'} eq "0"))
			{
			push @report,"imei cannot insert,some key is zero:$str";
			}
		#else insert
		else
			{
			my $sql_stmnt_insert = "INSERT INTO seed \($field_all\) VALUES \($str\)";
			my ($sth_insert,$result) = Run::ex($sql_stmnt_insert,$dbh);
			if ($result ne "1")
			{
			push @error, "ERROR:error to insert $str";
			}
			else
			{
			Run::finish($sth_insert);
			#push @insert_imei,$str;
			}
			}
		}
	#else imei exist,check keys one by one
	else
		{
		my $update = "0";
		my $pass = "0";
		my $report = "0";
		foreach my $k (@allkey)
			{
			($self->{$k}{'STATUS'},$self->{$k}{'DB'})= check($k,$self->{$k}{KEY},$dbh,$self->{'IMEI'});

			if ($self->{$k}{'STATUS'} eq "report")
				{
				my $re = $str;
                                $re = $re . "\($k=\"$self->{$k}{'KEY'}\"\)";
				push @report,"imei already exists,but keys are null/zero:$re";
				$report = "1";
				last;
				}
			elsif ($self->{$k}{'STATUS'} eq "diff")
				{
				my $re = $str;
				$re = $re . "\($k \"$self->{$k}{'DB'}\" != \"$self->{$k}{'KEY'}\"\)";
				push @report,"imei already exists,but keys are diff from database:$re";
				$report = "1";
				last;
				}
			elsif ($self->{$k}{'STATUS'} eq "update")
				{
				$update = "1";
				}
			elsif ($self->{$k}{'STATUS'} eq "pass")
				{
				$pass = "1";
				}
			}
		if (($report eq "0") and ($update eq "1"))
			{
			foreach my $k (@allkey)
	                        {
				if ($self->{$k}{'STATUS'} eq "update")
					{
					my $sql_stmnt_update = "UPDATE seed SET $k=\"$self->{$k}{'KEY'}\" WHERE IMEI=$self->{IMEI}";
	                                my ($sth_update,$result) = Run::ex($sql_stmnt_update,$dbh);
					if ($result ne "1" )
					{
					print "ERROR:error to update $str \($k\)\n";
					push @error, "ERROR:error to update $str \($k\)";
					}
					else
					{
        	                        Run::finish($sth_update);
					}
					}
				}
			#push @update_key,$re;
			}


		}
	}


if ( @report)
{
Log::print("report",@report);
}
if ( @error)
{
Log::print("error",@error);
}
if ( @format_error)
{
Log::print("format",@format_error);
}

Log::print("end");
my $logfile=Log::log_file;
print "Log file saved at $logfile\n";
Log::send_mail($logfile);

my $result = Connect::db_discon($dbh);


exit(0);


sub check
	{
	my $info;
	my ($field,$key,$dbh,$imei) = @_;

	my $sql_stmnt_check = "SELECT $field FROM seed WHERE IMEI=$imei";
	my ($sth_check,$result) = Run::ex($sql_stmnt_check,$dbh);
	if ($result ne "1")
	{
	push @error,"ERROR:error to get the data $field from database";
	}
	else
	{
	while (my @ref = $sth_check->fetchrow_array)
		{
		my $ref = shift @ref;
		#if keys not the same
		if ($ref ne $key)
			{
			#if key in file is null or 0,then report
			if(($key eq "") or ($key eq "0"))
				{
				$info = "report";
				Run::finish($sth_check);
				return ($info,$ref);
				}
			#if key in db is null or 0,then update
			elsif (($ref eq "") or ($ref eq "0"))
				{
				Run::finish($sth_check);
				$info = "update";
				return ($info,$ref);
				}
			else 
				{
				$info = "diff";
				Run::finish($sth_check);
				return ($info,$ref);
				}
			}
		else
			{
			$info = "pass";
			Run::finish($sth_check);
			return ($info,$ref);
			}
		}
	}

	}

